# -*- coding: utf-8 -*-
import pymysql
import configparser
import os


class Mysql():

    def __init__(self):
        config = configparser.ConfigParser()
        # pwd = os.getcwd()
        # file_path = pwd
        config.read(r".\config.ini")
        host = config.get('db', 'host')
        user = config.get('db', 'user')
        password = config.get('db', 'password')
        port = config.get('db', 'port')
        database = config.get('db', 'database')
        # 创建connection
        self.connection = pymysql.connect(host=host, user=user, password=password, port=int(port),
                                          database=database)
        # self.connection = pymysql.connect(host='localhost', user='root', password='123123', port=3307,
        #                                   database='jmeter_test')
        # 获取cursor
        self.cursor = self.connection.cursor()

    def closedatabase(self):
        """
        关闭数据库
        :return:
        """
        self.cursor.close()
        self.connection.close()

    def dropdatabase(self, sql):
        """
        删除
        :return:
        """
        try:
            self.cursor.execute(sql)
            print("数据表删除成功")
        except Exception as e:
            print("数据表删除失败：原因是%s" % e)
        finally:
            self.closedatabase()

    def create(self, sql):
        """
        创建数据表
        :return:
        """
        try:
            self.cursor.execute(sql)
            print("数据表创建成功")
        except Exception as e:
            print("数据表创建失败：原因是%s" % e)
        finally:
            self.closedatabase()

    def insert(self, sql):
        """
        插入数据
        :return:
        """
        try:
            self.cursor.execute(sql)
            self.connection.commit()  # 提交数据
            print("插入数据成功")
        except Exception as e:
            print("插入数据失败：原因是%s" % e)
            self.connection.rollback()  # 数据回滚
        finally:
            self.closedatabase()

    def delete(self, sql):
        """
        删除数据
        :return:
        """
        try:
            self.cursor.execute(sql)
            self.connection.commit()  # 提交数据
            print("删除数据成功")
        except Exception as e:
            print("删除数据失败：原因是%s" % e)
            self.connection.rollback()  # 数据回滚
        finally:
            self.closedatabase()

    def update(self, sql):
        """
        修改数据
        :return:
        """
        try:
            self.cursor.execute(sql)
            self.connection.commit()  # 提交数据
            print("修改数据成功")
        except Exception as e:
            print("修改数据失败：原因是%s" % e)
            self.connection.rollback()  # 数据回滚
        finally:
            self.closedatabase()

    def select(self, sql):
        """
        查询数据
        :return:
        """
        try:
            # self.cursor.execute(sql)
            """打印第一条数据：每打印一条数据，游标向后移动一位"""
            # a = self.cursor.fetchone()
            # print(a)
            """修改游标:将游标向后移动2位"""
            # self.cursor.scroll(2)
            """打印多条数据"""
            # b = self.cursor.fetchmany(3)
            # print(b)
            """将查询的数据以字典的形式返回（方便对数据内容进行查找）"""
            # self.cursor = self.connection.cursor(cursor=pymysql.cursors.DictCursor)
            # self.cursor.execute(sql)
            # res = self.cursor.fetchall()
            # # print(res)
            # print(res[1]["province"])
            """查询任意id的学生信息"""
            'or 1 = 1 or'  # sql注入
            # num = input("请输入要查询的学生信息的id：")
            # print(sql)
            # 防止SQL注入
            self.cursor.execute(sql)
            res = self.cursor.fetchall()
            print(res)

        except Exception as e:
            print("查询失败：原因是%s" % e)
        finally:
            self.closedatabase()


if __name__ == '__main__':
    num = int(input("请输入要查询的学生信息的id："))
    sql = 'select * from students where id = %s' % num
    print(sql)
    Mysql().select(sql)
